{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2025-09-18T08:43:57.444438400Z",
     "start_time": "2025-09-18T08:43:56.617875300Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "           Store  Employees    Manager      Since Flagship\n0       New York         10      Sarah 2018-07-20    False\n1  San Francisco         12     Neriah 2019-11-02  MISSING\n2        Chicago          4    Katelin 2020-01-31      NaN\n3         Boston          5  Georgiana 2017-04-01     True\n4  Washington DC          3       Evan        NaT    False\n5      Las Vegas         11       Paul 2020-01-06    False",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Store</th>\n      <th>Employees</th>\n      <th>Manager</th>\n      <th>Since</th>\n      <th>Flagship</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>New York</td>\n      <td>10</td>\n      <td>Sarah</td>\n      <td>2018-07-20</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>San Francisco</td>\n      <td>12</td>\n      <td>Neriah</td>\n      <td>2019-11-02</td>\n      <td>MISSING</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Chicago</td>\n      <td>4</td>\n      <td>Katelin</td>\n      <td>2020-01-31</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Boston</td>\n      <td>5</td>\n      <td>Georgiana</td>\n      <td>2017-04-01</td>\n      <td>True</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Washington DC</td>\n      <td>3</td>\n      <td>Evan</td>\n      <td>NaT</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Las Vegas</td>\n      <td>11</td>\n      <td>Paul</td>\n      <td>2020-01-06</td>\n      <td>False</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_excel('xl/stores.xlsx', sheet_name='2019', skiprows=1, usecols='B:F')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "outputs": [
    {
     "data": {
      "text/plain": "           Store  Employees    Manager      Since  Flagship\n0       New York         10      Sarah 2018-07-20     False\n1  San Francisco         12     Neriah 2019-11-02     False\n2        Chicago          4    Katelin 2020-01-31     False\n3         Boston          5  Georgiana 2017-04-01      True\n4  Washington DC          3       Evan        NaT     False\n5      Las Vegas         11       Paul 2020-01-06     False",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Store</th>\n      <th>Employees</th>\n      <th>Manager</th>\n      <th>Since</th>\n      <th>Flagship</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>New York</td>\n      <td>10</td>\n      <td>Sarah</td>\n      <td>2018-07-20</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>San Francisco</td>\n      <td>12</td>\n      <td>Neriah</td>\n      <td>2019-11-02</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Chicago</td>\n      <td>4</td>\n      <td>Katelin</td>\n      <td>2020-01-31</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Boston</td>\n      <td>5</td>\n      <td>Georgiana</td>\n      <td>2017-04-01</td>\n      <td>True</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Washington DC</td>\n      <td>3</td>\n      <td>Evan</td>\n      <td>NaT</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Las Vegas</td>\n      <td>11</td>\n      <td>Paul</td>\n      <td>2020-01-06</td>\n      <td>False</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def fix_missing(x):\n",
    "    return False if x in ['', 'MISSING'] else x\n",
    "\n",
    "\n",
    "df = pd.read_excel('xl/stores.xlsx', sheet_name='2019', skiprows=1, usecols='B:F',\n",
    "                   converters={'Flagship': fix_missing})\n",
    "df"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-09-18T08:48:14.229542100Z",
     "start_time": "2025-09-18T08:48:14.174573300Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 6 entries, 0 to 5\n",
      "Data columns (total 5 columns):\n",
      " #   Column     Non-Null Count  Dtype         \n",
      "---  ------     --------------  -----         \n",
      " 0   Store      6 non-null      object        \n",
      " 1   Employees  6 non-null      int64         \n",
      " 2   Manager    6 non-null      object        \n",
      " 3   Since      5 non-null      datetime64[ns]\n",
      " 4   Flagship   6 non-null      bool          \n",
      "dtypes: bool(1), datetime64[ns](1), int64(1), object(2)\n",
      "memory usage: 326.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-09-18T08:48:29.191850400Z",
     "start_time": "2025-09-18T08:48:29.116892300Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "outputs": [
    {
     "data": {
      "text/plain": "           Store  Employees    Manager      Since  Flagship\n0       New York         10      Sarah 2018-07-20     False\n1  San Francisco         12     Neriah 2019-11-02     False\n2        Chicago          4    Katelin 2020-01-31     False\n3         Boston          5  Georgiana 2017-04-01      True\n4  Washington DC          3       Evan        NaT     False\n5      Las Vegas         11       Paul 2020-01-06     False",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Store</th>\n      <th>Employees</th>\n      <th>Manager</th>\n      <th>Since</th>\n      <th>Flagship</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>New York</td>\n      <td>10</td>\n      <td>Sarah</td>\n      <td>2018-07-20</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>San Francisco</td>\n      <td>12</td>\n      <td>Neriah</td>\n      <td>2019-11-02</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Chicago</td>\n      <td>4</td>\n      <td>Katelin</td>\n      <td>2020-01-31</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Boston</td>\n      <td>5</td>\n      <td>Georgiana</td>\n      <td>2017-04-01</td>\n      <td>True</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Washington DC</td>\n      <td>3</td>\n      <td>Evan</td>\n      <td>NaT</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Las Vegas</td>\n      <td>11</td>\n      <td>Paul</td>\n      <td>2020-01-06</td>\n      <td>False</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-09-18T08:55:30.981011600Z",
     "start_time": "2025-09-18T08:55:30.889064600Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "outputs": [],
   "source": [
    "df2 = pd.read_excel('xl/stores.xlsx')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-09-18T08:56:02.773777Z",
     "start_time": "2025-09-18T08:56:02.684380Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "outputs": [
    {
     "data": {
      "text/plain": "   Unnamed: 0     Unnamed: 1 Unnamed: 2 Unnamed: 3           Unnamed: 4  \\\n0         NaN          Store  Employees    Manager                Since   \n1         NaN       New York         10      Sarah  2018-07-20 00:00:00   \n2         NaN  San Francisco         12     Neriah  2019-11-02 00:00:00   \n3         NaN        Chicago          4    Katelin  2020-01-31 00:00:00   \n4         NaN         Boston          5  Georgiana  2017-04-01 00:00:00   \n5         NaN  Washington DC          3       Evan                  NaN   \n6         NaN      Las Vegas         11       Paul  2020-01-06 00:00:00   \n\n  Unnamed: 5  \n0   Flagship  \n1      False  \n2    MISSING  \n3        NaN  \n4       True  \n5      False  \n6      False  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Unnamed: 0</th>\n      <th>Unnamed: 1</th>\n      <th>Unnamed: 2</th>\n      <th>Unnamed: 3</th>\n      <th>Unnamed: 4</th>\n      <th>Unnamed: 5</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>NaN</td>\n      <td>Store</td>\n      <td>Employees</td>\n      <td>Manager</td>\n      <td>Since</td>\n      <td>Flagship</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>NaN</td>\n      <td>New York</td>\n      <td>10</td>\n      <td>Sarah</td>\n      <td>2018-07-20 00:00:00</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>NaN</td>\n      <td>San Francisco</td>\n      <td>12</td>\n      <td>Neriah</td>\n      <td>2019-11-02 00:00:00</td>\n      <td>MISSING</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>NaN</td>\n      <td>Chicago</td>\n      <td>4</td>\n      <td>Katelin</td>\n      <td>2020-01-31 00:00:00</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>NaN</td>\n      <td>Boston</td>\n      <td>5</td>\n      <td>Georgiana</td>\n      <td>2017-04-01 00:00:00</td>\n      <td>True</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>NaN</td>\n      <td>Washington DC</td>\n      <td>3</td>\n      <td>Evan</td>\n      <td>NaN</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>NaN</td>\n      <td>Las Vegas</td>\n      <td>11</td>\n      <td>Paul</td>\n      <td>2020-01-06 00:00:00</td>\n      <td>False</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-09-18T08:56:05.343443600Z",
     "start_time": "2025-09-18T08:56:05.270822500Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "outputs": [
    {
     "data": {
      "text/plain": "           Store  Employees Flagship\n0       New York         10    False\n1  San Francisco         12      NaN\n2        Chicago          4         \n3         Boston          5     True",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Store</th>\n      <th>Employees</th>\n      <th>Flagship</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>New York</td>\n      <td>10</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>San Francisco</td>\n      <td>12</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Chicago</td>\n      <td>4</td>\n      <td></td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Boston</td>\n      <td>5</td>\n      <td>True</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('xl/stores.xlsx', sheet_name='2019', skiprows=1, usecols='B,C,F', skipfooter=2, na_values='MISSING',\n",
    "                   keep_default_na=False)\n",
    "df"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-09-18T08:57:41.873001400Z",
     "start_time": "2025-09-18T08:57:41.752069900Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "outputs": [
    {
     "data": {
      "text/plain": "           Store  Employees Manager      Since Flagship\n0       New York         10   Sarah 2018-07-20    False\n1  San Francisco         12  Neriah 2019-11-02  MISSING",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Store</th>\n      <th>Employees</th>\n      <th>Manager</th>\n      <th>Since</th>\n      <th>Flagship</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>New York</td>\n      <td>10</td>\n      <td>Sarah</td>\n      <td>2018-07-20</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>San Francisco</td>\n      <td>12</td>\n      <td>Neriah</td>\n      <td>2019-11-02</td>\n      <td>MISSING</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with pd.ExcelFile('xl/stores.xls') as f:\n",
    "    df1 = pd.read_excel(f, '2019', skiprows=1, usecols='B:F', nrows=2)\n",
    "    df2 = pd.read_excel(f, '2020', skiprows=1, usecols='B:F', nrows=2)\n",
    "df1"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-09-18T09:08:19.298693100Z",
     "start_time": "2025-09-18T09:08:19.248725300Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
